/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.lct.eq2.multicast;

import com.lct.eq2.data.AttackContainer;
import com.lct.eq2.data.DataHandler;
import com.lct.eq2.data.Line;
import com.lct.eq2.util.DailyLog;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.LinkedList;
import java.util.Properties;
import java.util.Queue;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author waite
 */
public class DatabaseHelperThread extends Thread {
    private DailyLog logger = DailyLog.getInstance("logs", "DatabaseHelperThread");
    
    private Queue storageQueue;
    private Connection conn;
    private DataHandler dataHandler;
    private boolean running;

    public DatabaseHelperThread() {
        System.out.println("DatabaseHelperThread("+this.getId()+") - Started...");
        conn = this.openConnection();
        dataHandler = new DataHandler();
        this.storageQueue = new LinkedList();
    }

    public Connection openConnection() {
        Connection rval = null;

        String username;
        String password;
        String url;

        Properties props = new Properties();
        try {
            props.load(new FileInputStream(new File("database.properties")));
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }

        username = props.getProperty("username");
        password = props.getProperty("password");
        url = props.getProperty("url");

        String driverName = "com.mysql.jdbc.Driver";
        try {
            Class.forName(driverName);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }

        try {
            rval = DriverManager.getConnection(url, username, password);
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return rval;
    }

    public void closeConnection(Connection conn) {
        try {
            conn.close();
        } catch (NullPointerException e) {
        // connection not open
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public Stats getCharacterStats(int characterID) {
        Stats rval = new Stats();
        String sql = "SELECT * from character_stats where character_id=" + characterID;
        ResultSet rs = this.executeSQL(sql);
        try {
            while (rs.next()) {
                rval.setCharID(characterID);
                rval.setTotalOutgoingAttacks(rs.getInt("total_outgoing_attacks"));
                rval.setTotalIncomingAttacks(rs.getInt("total_incoming_attacks"));
                rval.setTotalIncomingAvoids(rs.getInt("total_incoming_avoids"));
                rval.setTotalOutgoingAvoids(rs.getInt("total_outgoing_avoids"));
                rval.setTotalOutgoingDamage(rs.getInt("total_outgoing_damage"));
                rval.setTotalDeaths(rs.getInt("total_deaths"));
                rval.setTotalIncomingBlocks(rs.getInt("total_incoming_blocks"));
                rval.setTotalIncomingDisrupts(rs.getInt("total_incoming_disrupts"));
                rval.setTotalIncomingDodges(rs.getInt("total_incoming_dodges"));
                rval.setTotalIncomingParries(rs.getInt("total_incoming_parries"));
                rval.setTotalOutgoingBlocks(rs.getInt("total_outgoing_blocks"));
                rval.setTotalOutgoingDisrupts(rs.getInt("total_outgoing_disrupts"));
                rval.setTotalOutgoingDodges(rs.getInt("total_outgoing_dodges"));
                rval.setTotalOutgoingParries(rs.getInt("total_outgoing_parries"));
                rval.setTotalIncomingDamage(rs.getInt("total_incoming_damage"));
                rval.setRank(rs.getInt("total_rank"));
                rval.setRenownRank(rs.getInt("total_renown_rank"));
                rval.setTotalIncomingCrits(rs.getInt("total_incoming_crits"));
                rval.setTotalOutgoingCrits(rs.getInt("total_outgoing_crits"));
                rval.setTotalSecondsFighting(rs.getLong("total_seconds_fighting"));
                
                rval.setHighestIncomingMeleeHit(rs.getLong("highest_incoming_melee"));
                rval.setLowestIncomingMeleeHit(rs.getLong("lowest_incoming_melee"));
                rval.setHighestIncomingNonMeleeHit(rs.getLong("highest_incoming_non_melee"));
                rval.setLowestIncomingNonMeleeHit(rs.getLong("lowest_incoming_non_melee"));
                rval.setHighestOutgoingMeleeHit(rs.getLong("highest_outgoing_melee"));
                rval.setLowestOutgoingMeleeHit(rs.getLong("lowest_outgoing_melee"));
                rval.setHighestOutgoingNonMeleeHit(rs.getLong("highest_outgoing_non_melee"));
                rval.setLowestOutgoingNonMeleeHit(rs.getLong("lowest_outgoing_non_melee"));
                rval.setHighestIncomingHeal(rs.getLong("highest_incoming_heal"));
                rval.setHighestOutgoingHeal(rs.getLong("highest_outgoing_heal"));
                rval.setTotalIncomingHealing(rs.getLong("total_incoming_healing"));
                rval.setTotalOutgoingHealing(rs.getLong("total_outgoing_healing"));
                rval.setCareer(rs.getString("career_name"));
            }
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseHelperThread.class.getName()).log(Level.SEVERE, null, ex);
        }
        return rval;
    }
    
    public void updateCharacterStats(ArrayList list, int characterID, String characterName) {
        long before = System.currentTimeMillis();
        Stats charStats = this.getCharacterStats(characterID);
        long secondsFighting = 0;
        long beginMillis = 0;
        
        for (int x=0; x < list.size(); x++) {
            AttackContainer ac = (AttackContainer)list.get(x);
            if (x==0) {
                beginMillis = ac.getTimeStamp();
            } else if (x == list.size()-1) {
                secondsFighting = (ac.getTimeStamp() / 1000) - (beginMillis / 1000);
                //System.out.println(secondsFighting);
                charStats.addToTotalSecondsFighting(secondsFighting);
            }
            if (ac.getAttackDirectionIntRelativeToCombatant(characterName) == AttackContainer.ATTACK_TYPE_OUTGOING) {
                if ((charStats.getCareer() != null && charStats.getCareer().equalsIgnoreCase("") || charStats.getCareer() == null) && !ac.getAttackName().equalsIgnoreCase("attack") && 
                        ac.getAttackTypeInt() != AttackContainer.ATTACK_TYPE_UNKNOWN) {
//                    charStats.setCareer(this.dataHandler.getCareerName(ac.getAttackName()));
                }
                charStats.addToTotalOutgoingAttacks(1);
                
                if (ac.isAvoided()) {
                    charStats.addToTotalOutgoingAvoids(1);
                    switch (ac.getReasonAvoided()) {
                        case AttackContainer.AVOID_TYPE_MELEE_BLOCK:
                            charStats.addToTotalOutgoingBlocks(1);
                            break;
                        case AttackContainer.AVOID_TYPE_MELEE_PARRY:
                            charStats.addToTotalOutgoingParries(1);
                            break;
                        case AttackContainer.AVOID_TYPE_MELEE_DODGE:
                            charStats.addToTotalOutgoingDodges(1);
                            break;
                        case AttackContainer.AVOID_TYPE_NON_MELEE_RESIST:
                            charStats.addToTotalOutgoingDisrupts(1);
                            break;
                        default:
                            break;
                    }
                } else {
                    if (!ac.isHeal()) {
                        if (ac.isMelee()) {
                            if (ac.getAmountOfAttackDamage() > charStats.getHighestOutgoingMeleeHit()) {
                                charStats.setHighestOutgoingMeleeHit(ac.getAmountOfAttackDamage());
                            }
                        } else if (ac.isNonMelee()) {
                            if (ac.getAmountOfAttackDamage() > charStats.getHighestOutgoingNonMeleeHit()) {
                                charStats.setHighestOutgoingNonMeleeHit(ac.getAmountOfAttackDamage());
                            }
                        }
                    } else {
                        charStats.setTotalOutgoingHealing(charStats.getTotalOutgoingHealing() + ac.getAmountOfAttackDamage());
                        if (ac.getAmountOfAttackDamage() > charStats.getHighestOutgoingHeal()) {
                            charStats.setHighestOutgoingHeal(ac.getAmountOfAttackDamage());
                        }
                    }
                    
                    charStats.addToTotalOutgoingDamage((int)ac.getAmountOfAttackDamage());
                }
                if (ac.isCrit()) {
                    charStats.addToTotalOutgoingCrits(1);
                }
            } else if (ac.getAttackDirectionIntRelativeToCombatant(characterName) == AttackContainer.ATTACK_TYPE_INCOMING) {
                if (ac.isAvoided()) {
                    charStats.addToTotalIncomingAvoids(1);
                    switch (ac.getReasonAvoided()) {
                        case AttackContainer.AVOID_TYPE_MELEE_BLOCK:
                            charStats.addToTotalIncomingBlocks(1);
                            break;
                        case AttackContainer.AVOID_TYPE_MELEE_PARRY:
                            charStats.addToTotalIncomingParries(1);
                            break;
                        case AttackContainer.AVOID_TYPE_MELEE_DODGE:
                            charStats.addToTotalIncomingDodges(1);
                            break;
                        case AttackContainer.AVOID_TYPE_NON_MELEE_RESIST:
                            charStats.addToTotalIncomingDisrupts(1);
                            break;
                        default:
                            break;
                    }
                } else {
                    if (!ac.isHeal()) {
                        if (ac.isMelee()) {
                            if (ac.getAmountOfAttackDamage() > charStats.getHighestIncomingMeleeHit()) {
                                charStats.setHighestIncomingMeleeHit(ac.getAmountOfAttackDamage());
                            }
                        } else if (ac.isNonMelee()) {
                            if (ac.getAmountOfAttackDamage() > charStats.getHighestIncomingNonMeleeHit()) {
                                charStats.setHighestIncomingNonMeleeHit(ac.getAmountOfAttackDamage());
                            }
                        }
                    } else {
                        charStats.setTotalIncomingHealing(charStats.getTotalIncomingHealing() + ac.getAmountOfAttackDamage());
                        if (ac.getAmountOfAttackDamage() > charStats.getHighestIncomingHeal()) {
                            charStats.setHighestIncomingHeal(ac.getAmountOfAttackDamage());
                        }
                    }
                    
                    charStats.addToTotalIncomingDamage((int)ac.getAmountOfAttackDamage());
                }
                charStats.addToTotalIncomingAttacks(1);
                if (ac.isCrit()) {
                    charStats.addToTotalIncomingCrits(1);
                }
            }
        }

        String[] paramNames = {"total_outgoing_damage", "total_incoming_damage", "total_incoming_avoids", "total_outgoing_avoids", "total_incoming_crits",
            "total_outgoing_crits", "total_incoming_attacks", "total_outgoing_attacks", "total_incoming_parries", "total_incoming_blocks", 
            "total_incoming_dodges", "total_incoming_disrupts", "total_outgoing_parries", "total_outgoing_blocks", 
            "total_outgoing_dodges", "total_outgoing_disrupts", "total_seconds_fighting", "highest_outgoing_melee", 
            "highest_incoming_melee", "highest_outgoing_non_melee", "highest_incoming_non_melee", "highest_outgoing_heal",
            "highest_incoming_heal", "total_incoming_healing", "total_outgoing_healing", "career_name"};
        
        Object[] o = {charStats.getTotalOutgoingDamage(), charStats.getTotalIncomingDamage(),
            charStats.getTotalIncomingAvoids(), charStats.getTotalOutgoingAvoids(), charStats.getTotalIncomingCrits(), 
            charStats.getTotalOutgoingCrits(), charStats.getTotalIncomingAttacks(), charStats.getTotalOutgoingAttacks(), 
            charStats.getTotalIncomingParries(), charStats.getTotalIncomingBlocks(), charStats.getTotalIncomingDodges(), 
            charStats.getTotalIncomingDisrupts(), charStats.getTotalOutgoingParries(), charStats.getTotalOutgoingBlocks(),
            charStats.getTotalOutgoingDodges(), charStats.getTotalOutgoingDisrupts(), charStats.getTotalSecondsFighting(), 
            charStats.getHighestOutgoingMeleeHit(), charStats.getHighestIncomingMeleeHit(), charStats.getHighestOutgoingNonMeleeHit(),
            charStats.getHighestIncomingNonMeleeHit(), charStats.getHighestOutgoingHeal(), charStats.getHighestIncomingHeal(),
            charStats.getTotalIncomingHealing(), charStats.getTotalOutgoingHealing(), charStats.getCareer(), characterID};
        
        this.updateNCharacterStats(paramNames, o);
        
        this.logger.info("DatabaseHelperThread", "updateCharacterStats()", "Took "
                + (System.currentTimeMillis() - before) + "ms to update total stats for user "
                + charStats.getCharID());
    }
    
    private void updateNCharacterStats(String[] paramNames, Object[] o) {
        String sql = "UPDATE warct.character_stats SET ";
        for (int x=0; x < paramNames.length; x++) {
            if (x==0) {
                sql += paramNames[x] + "=?";
            } else {
                sql += ", " + paramNames[x] + "=?";
            }
        }
        sql += " WHERE character_id=?";
        this.executePreparedStatement(sql, o);
    }
    
    public int getCharacterID(String charName, String serverName) {
        int rval = -1;
        try {
            int serverID = this.getServerID(serverName);
            String charIDSql = "SELECT character_id " + "FROM character_data where character_name='" + charName + "' " +
                    "AND server_id=" + serverID;
            //System.out.println(charIDSql);

            ResultSet rs = this.executeSQL(charIDSql);

            if (rs.next()) {
                rval = rs.getInt("character_id");
            } else {
                String insertSQL = "INSERT into character_data (character_name, server_id) " +
                        "values ('" + charName + "'," + serverID + ")";
                rs = this.executeSQL(insertSQL);

                if (rs.next()) {
                    rval = rs.getInt(1);
                    insertSQL = "INSERT into character_stats (character_id) " +
                        "values (" + rval + ")";
                    this.executeSQL(insertSQL);
                }
            }
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseHelperThread.class.getName()).log(Level.SEVERE, null, ex);
        }
        return rval;
    }

    public int getServerID(String serverName) {
        int rval = -1;
        try {
            String serverIDSql = "SELECT server_id " + "FROM servers where server_name='" + serverName + "'";

            ResultSet rs = this.executeSQL(serverIDSql);

            if (rs.next()) {
                rval = rs.getInt("server_id");
            } else {
                String insertSQL = "INSERT into servers (server_name) values ('" + serverName + "')";
                rs = this.executeSQL(insertSQL);

                if (rs.next()) {
                    rval = rs.getInt(1);
                }
            }
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseHelperThread.class.getName()).log(Level.SEVERE, null, ex);
        }
        return rval;
    }

    public void addAttackToQueue(String attack, String charName, String serverName) {
//        AttackContainer a = dataHandler.getCombatLineInfo(attack,
//                dataHandler.checkForCombatLine(attack, false), charName, serverName);
//        this.storageQueue.offer(a);
    }

    public void addAttackListToQueue(ArrayList list) {
        this.storageQueue.offer(list);
    }

    public ResultSet executePreparedStatement(String sql, ArrayList data) {
        ResultSet rval = null;
        try {
//            String[] columnNames = {"att_damage", "att_name", "att_type",
//            "att_aer", "att_avoided", "att_avoid_reason", "att_is_crit", "character_id", 
//            "att_target", "att_timestamp", "ref_encounter_id"};
            //PreparedStatement stmt = this.conn.prepareStatement(sql);
            PreparedStatement stmt = this.conn.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);
            int count = 0;
            for (Object object : data) {
                Object[] params = (Object[])object;
                for (int x = 0; x < params.length; x++) {
                    Object param = params[x];
                    stmt.setObject(++count, param);
                }
            }
            
            stmt.executeUpdate();
            rval = stmt.getGeneratedKeys();
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseHelperThread.class.getName()).log(Level.SEVERE, null, ex);
        }
        return rval;
    }
    
    public void executeUpdatePreparedStatement(String sql, Object[] data) {
        ResultSet rval = null;
        try {
//            String[] columnNames = {"att_damage", "att_name", "att_type",
//            "att_attacker", "att_avoided", "att_avoid_reason", "att_is_crit", "character_id", 
//            "att_target", "att_timestamp", "ref_encounter_id"};
            //PreparedStatement stmt = this.conn.prepareStatement(sql);
            PreparedStatement stmt = this.conn.prepareStatement(sql);
            for (int x = 0; x < data.length; x++) {
                Object object = data[x];
                stmt.setObject(x + 1, object);
            }
            
            stmt.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseHelperThread.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    
    public ResultSet executePreparedStatement(String sql, Object[] data) {
        ResultSet rval = null;
        try {
//            String[] columnNames = {"att_damage", "att_name", "att_type",
//            "att_attacker", "att_avoided", "att_avoid_reason", "att_is_crit", "character_id", 
//            "att_target", "att_timestamp", "ref_encounter_id"};
            //PreparedStatement stmt = this.conn.prepareStatement(sql);
            PreparedStatement stmt = this.conn.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);
            for (int x = 0; x < data.length; x++) {
                Object object = data[x];
                stmt.setObject(x + 1, object);
            }
            
            stmt.executeUpdate();
            rval = stmt.getGeneratedKeys();
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseHelperThread.class.getName()).log(Level.SEVERE, null, ex);
        }
        return rval;
    }

    public ResultSet executeSQL(String sql) {
        ResultSet rval = null;
        try {
            Statement stmt = this.conn.createStatement();
            if (sql.contains("SELECT")) {
                rval = stmt.executeQuery(sql);
            } else if (sql.contains("INSERT")) {
                stmt.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);
                rval = stmt.getGeneratedKeys();
            }
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseHelperThread.class.getName()).log(Level.SEVERE, null, ex);
        }
        return rval;
    }

    public void stopRunning() {
        this.running = false;
        this.closeConnection(conn);
    }
    
    public void increaseRank(Line line) {
        int charID = -1;
        String sql = "UPDATE warct.character_stats set ";
        
        switch (line.getType()) {
            case Line.RENOWN_RANK_INCREASE:
                charID = this.getCharacterID(line.getCharacter(), line.getServer());
                String[] paramNames1 = {"total_renown_rank"};
                Object[] data1 = {Integer.parseInt(line.getLine()), charID};
                this.updateNCharacterStats(paramNames1, data1);
                break;
            case Line.RANK_INCREASE:
                charID = this.getCharacterID(line.getCharacter(), line.getServer());
                String[] paramNames2 = {"total_rank"};
                Object[] data2 = {Integer.parseInt(line.getLine()), charID};
                this.updateNCharacterStats(paramNames2, data2);
                break;
            default:
                break;
        }
    }

    @Override
    public void run() {
        this.running = true;
        int firstInsertedID = -1;
        int lastInsertedID = -1;

        while (running) {
            if (!this.storageQueue.isEmpty()) {
                Object o = this.storageQueue.poll();
                if (o instanceof ArrayList) {
                    
                    long before = System.currentTimeMillis();
                    ArrayList list = (ArrayList) o;
                    
                    String sql = "INSERT INTO warct.attacks (att_damage, att_name, att_type, att_attacker, "
                            + "att_avoided, att_avoid_reason, att_is_crit, character_id, att_target, att_timestamp"
                            + ") VALUES";
                    String questionMarks = " (?,?,?,?,?,?,?,?,?,?)";
                    int charID = -1;
                    String charName = null;
                    String serverName = null;
                    ResultSet rs = null;
                    ArrayList paramList = new ArrayList();
                    ArrayList attacks = new ArrayList();;
                    
                    int z = 0;
                    
                    for (Object object : list) {
                        Line line = (Line)object;
                        String data = line.getLine();
                        if (charName == null) {   
                            charName = line.getCharacter();
                            serverName = line.getServer();
                            charID = this.getCharacterID(charName, serverName);
                        }
                        
                        //System.out.println(data);
                        if (data.lastIndexOf(charName + "'s") <= 20) {
                            data = data.replaceAll(charName + "'s ", "Your ");
                        } else {
                            data = data.replaceAll(charName + "'s ", "your ");
                        }
                        if (data.indexOf(charName) > 20) {
                            if (!(data.lastIndexOf(charName) > data.indexOf(charName)) && !(data.lastIndexOf(charName) > data.indexOf("'s"))) {
                                data = data.replaceAll(charName + "'s", "your");
                            }
                            data = data.replaceAll(charName, "you");
                        } else {
                            if (data.contains(" hits")) {
                                data = data.replaceAll(charName, "Your");
                            }
                            data = data.replaceAll(charName, "You");
                        }
                        
                        //System.out.println("DatabaseHelperThread::run() - data = " + data);
//                        try {
//                            AttackContainer ac = dataHandler.getCombatLineInfo(data, dataHandler.checkForCombatLine(data, false), charName, serverName);
//                            if (!ac.getAttackName().equalsIgnoreCase("bolster")) {
//                                attacks.add(ac);
//                                //System.out.println("DatabaseHelperThread::run() - " + ac.getAttacker() + " dealt " + ac.getAmountOfAttackDamage() + " damage to " + ac.getTarget() + " with " + ac.getAttackName() + ", " + ac.getAttackType() + " " + ac.getAttackClass() + " damage.");
//                                Object[] params = {ac.getAmountOfAttackDamage(), ac.getAttackName(), ac.getAttackType(), ac.getAttacker(),
//                                    ac.isAvoided(), ac.getReasonAvoided(), ac.isCrit(), charID, ac.getTarget(), ac.getTimeStamp()};
//                                paramList.add(params);
//                                sql += (z != 0 ? "," : "") + questionMarks;
//                                z++;
//                            }
//                        } catch (Exception ex) {
//                            logger.error("DatabaseHelperThread", "run()", ex);
//                        }
                    }
                    
                    try {
                        rs = this.executePreparedStatement(sql, paramList);
                        this.updateCharacterStats(attacks, charID, charName);

                        rs.first();
                        firstInsertedID = rs.getInt(1);
                        rs.last();

                        int id = rs.getInt(1);
                        lastInsertedID = id;
                    } catch (SQLException ex) {
                        Logger.getLogger(DatabaseHelperThread.class.getName()).log(Level.SEVERE, null, ex);
                    }
                    if (firstInsertedID != -1) {
                        sql = "INSERT into encounters (attack_id_start, attack_id_end, character_id) values (" 
                                + firstInsertedID + "," + lastInsertedID + "," + charID + ")";
                        this.executeSQL(sql);
                        firstInsertedID = -1;
                        lastInsertedID = -1;
                    }
                    if (this.storageQueue.size() > 0) {
                        logger.info("DatabaseHelperThread", "run()", "Queue behind by: " + this.storageQueue.size() + " Objects.");
                    }
                    logger.info("DatabaseHelperThread", "run()", "It took " + (System.currentTimeMillis() - before) + "ms to create rows.");
                }
            } else {
                try {
                    Thread.currentThread().sleep(50);
                } catch (InterruptedException ex) {
                    Logger.getLogger(DatabaseHelperThread.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
    }
}
